運用 where... in...和 join

第 11 屆 iThome 鐵人賽

DAY 6

自我挑戰組

MySQL 學習筆記系列第 6 篇

11th鐵人賽

stca

2019-09-22 10:30:09

877 瀏覽

分享至

前言

我們已經對手中的北風資料庫有了基本的認識，接下來我們來做一些用口語表達聽似很複雜，轉換成指令卻很簡單的查詢動作。

列出suppliers(供應商)表單中的supplierid及company欄位的資料

MariaDB [northwind]> select supplierid,companyname from suppliers;
+------------+----------------------------------------+
| supplierid | companyname                            |
+------------+----------------------------------------+
|          1 | Exotic Liquids                         |
|          2 | New Orleans Cajun Delights             |
|          3 | Grandma Kelly's Homestead              |
|          4 | Tokyo Traders                          |
|          5 | Cooperativa de Quesos 'Las Cabras'     |
|          6 | Mayumi's                               |
|          7 | Pavlova, Ltd.                          |
|          8 | Specialty Biscuits, Ltd.               |
|          9 | PB Knackebrod AB                       |
|         10 | Refrescos Americanas LTDA              |
|         11 | Heli Suswaren GmbH & Co. KG            |
|         12 | Plutzer Lebensmittelgrosmarkte AG      |
|         13 | Nord-Ost-Fisch Handelsgesellschaft mbH |
|         14 | Formaggi Fortini s.r.l.                |
|         15 | Norske Meierier                        |
|         16 | Bigfoot Breweries                      |
|         17 | Svensk Sjofoda AB                      |
|         18 | Aux joyeux ecclesiastiques             |
|         19 | New England Seafood Cannery            |
|         20 | Leka Trading                           |
|         21 | Lyngbysild                             |
|         22 | Zaanse Snoepfabriek                    |
|         23 | Karkki Oy                              |
|         24 | G'day, Mate                            |
|         25 | Ma Maison                              |
|         26 | Pasta Buttini s.r.l.                   |
|         27 | Escargots Nouveaux                     |
|         28 | Gai paturage                           |
|         29 | Forets d'erables                       |
+------------+----------------------------------------+
29 rows in set (0.000 sec)

列出編號供應商id為21號販售給我們那些商品

MariaDB [northwind]>  select productname from products where supplierid=21;
+-------------+
| productname |
+-------------+
| Rogede sild |
| Spegesild   |
+-------------+
2 rows in set (0.001 sec)

列出供應商名字為Lyngbysild販售給我們公司那些商品

MariaDB [northwind]>  select productname from products where supplierid=(
    -> select supplierid from suppliers where companyname ='Lyngbysild');
+-------------+
| productname |
+-------------+
| Rogede sild |
| Spegesild   |
+-------------+
2 rows in set (0.001 sec)
(
# 挑三間公司以上公司來查詢販售給我們公司那些商品 (用 in 來做)
MariaDB [northwind]> select productname from products where supplierid in
    -> (select supplierid from suppliers where companyname in
    -> ('Lyngbysild','Ma Maison','karkki Oy'));
+------------------+
| productname      |
+------------------+
| Rogede sild      |
| Spegesild        |
| Maxilaku         |
| Valkoinen suklaa |
| Tourtiere        |
| Pate chinois     |
| Lakkalikoori     |
+------------------+
7 rows in set (0.051 sec)

查詢海鮮類產品有哪些印出商品名稱

MariaDB [northwind]> select productname from products
    -> where categoryid =
    -> (select categoryid from categories
    -> where categoryname = 'Seafood');
+---------------------------------+
| productname                     |
+---------------------------------+
| Ikura                           |
| Konbu                           |
| Carnarvon Tigers                |
| Nord-Ost Matjeshering           |
| Inlagd Sill                     |
| Gravad lax                      |
| Boston Crab Meat                |
| Jack's New England Clam Chowder |
| Rogede sild                     |
| Spegesild                       |
| Escargots de Bourgogne          |
| Rod Kaviar                      |
+---------------------------------+
12 rows in set (0.001 sec)

請問提供分類代碼為8號給我們公司的有那些供應商

MariaDB [northwind]> select companyname from suppliers
    -> where supplierid  in
    -> (select supplierid from products
    -> where categoryid = 8);
+----------------------------------------+
| companyname                            |
+----------------------------------------+
| Tokyo Traders                          |
| Mayumi's                               |
| Pavlova, Ltd.                          |
| Nord-Ost-Fisch Handelsgesellschaft mbH |
| Svensk Sjofoda AB                      |
| New England Seafood Cannery            |
| Lyngbysild                             |
| Escargots Nouveaux                     |
+----------------------------------------+
8 rows in set (0.001 sec)

請問提供海鮮產品給我們公司的有那些供應商

MariaDB [northwind]> select companyname from suppliers
    -> where supplierid in
    -> (select supplierid from products
    -> where categoryid=
    -> (select categoryid from categories
    -> where categoryname='seafood'));
+----------------------------------------+
| companyname                            |
+----------------------------------------+
| Tokyo Traders                          |
| Mayumi's                               |
| Pavlova, Ltd.                          |
| Nord-Ost-Fisch Handelsgesellschaft mbH |
| Svensk Sjofoda AB                      |
| New England Seafood Cannery            |
| Lyngbysild                             |
| Escargots Nouveaux                     |
+----------------------------------------+
8 rows in set (0.002 sec)

join練習

幫我列出員工編號、lastname，在訂單表單裡面，有哪些訂單是哪一位員工做的?

MariaDB [northwind]> select employees.employeeid , employees.lastname,
    -> orders.orderid , orders.orderdate
    -> from employees
    -> join orders on
    -> (employees.employeeid = orders.employeeid)
    -> order by orders.orderdate;

+------------+-----------+---------+---------------------+
| employeeid | lastname  | orderid | orderdate           |
+------------+-----------+---------+---------------------+
|          5 | Buchanan  |   10248 | 1996-07-04 00:00:00 |
....中間省略
|          8 | Callahan  |   10262 | 1996-07-22 00:00:00 |
|          2 | Fuller    |   11070 | 1998-05-05 00:00:00 |
|          1 | Davolio   |   11071 | 1998-05-05 00:00:00 |
|          8 | Callahan  |   11075 | 1998-05-06 00:00:00 |
|          4 | Peacock   |   11076 | 1998-05-06 00:00:00 |
|          1 | Davolio   |   11077 | 1998-05-06 00:00:00 |
|          7 | Queen     |   11074 | 1998-05-06 00:00:00 |
+------------+-----------+---------+---------------------+
830 rows in set (0.059 sec)

承上，利用別名來做呢?

MariaDB [northwind]> select e.employeeid, e.lastname,
    -> o.orderid,o.orderdate
    -> from employees e
    -> join orders o on
    -> (e.employeeid = o.employeeid)
    -> order by o.orderdate;

+------------+-----------+---------+---------------------+
| employeeid | lastname  | orderid | orderdate           |
+------------+-----------+---------+---------------------+
|          5 | Buchanan  |   10248 | 1996-07-04 00:00:00 |
....中間省略
|          8 | Callahan  |   10262 | 1996-07-22 00:00:00 |
|          2 | Fuller    |   11070 | 1998-05-05 00:00:00 |
|          1 | Davolio   |   11071 | 1998-05-05 00:00:00 |
|          8 | Callahan  |   11075 | 1998-05-06 00:00:00 |
|          4 | Peacock   |   11076 | 1998-05-06 00:00:00 |
|          1 | Davolio   |   11077 | 1998-05-06 00:00:00 |
|          7 | Queen     |   11074 | 1998-05-06 00:00:00 |
+------------+-----------+---------+---------------------+
830 rows in set (0.059 sec)

join練習，先按員工編號排序在按日期

MariaDB [northwind]> select e.employeeid, e.lastname,
    -> o.orderid,o.orderdate
    -> from employees e
    -> join orders o on
    -> (e.employeeid = o.employeeid)
    -> order  by e.employeeid, o.orderdate;
+------------+-----------+---------+---------------------+
| employeeid | lastname  | orderid | orderdate           |
+------------+-----------+---------+---------------------+
|          1 | Davolio   |   10258 | 1996-07-17 00:00:00 |
|          1 | Davolio   |   10270 | 1996-08-01 00:00:00 |
|          1 | Davolio   |   10275 | 1996-08-07 00:00:00 |
|          1 | Davolio   |   10285 | 1996-08-20 00:00:00 |
|          1 | Davolio   |   10292 | 1996-08-28 00:00:00 |
|          1 | Davolio   |   10293 | 1996-08-29 00:00:00 |
|          1 | Davolio   |   10304 | 1996-09-12 00:00:00 |
2,3,4,5,6,7,8....中間省略
|          9 | Dodsworth |   10970 | 1998-03-24 00:00:00 |
|          9 | Dodsworth |   10978 | 1998-03-26 00:00:00 |
|          9 | Dodsworth |   11016 | 1998-04-10 00:00:00 |
|          9 | Dodsworth |   11017 | 1998-04-13 00:00:00 |
|          9 | Dodsworth |   11022 | 1998-04-14 00:00:00 |
|          9 | Dodsworth |   11058 | 1998-04-29 00:00:00 |
+------------+-----------+---------+---------------------+
830 rows in set (0.004 sec)

註:join寫到哪裡，關係先發生；
單引號、雙引號'" 指的是字串的值庫名稱表明稱用` 倒引號

題目:1988年之後寄出的日期超過原本預計的日期的訂單

MariaDB [northwind]> select o.orderid,c.companyname,e.lastname           
    -> from orders o                                       
    -> join employees e on (e.employeeid = o.employeeid)   
    -> join customers c on (c.customerid = o.customerid)， 
    -> where o.shippeddate > o.requireddate and    
    -> o.orderdate > '1988-01-01'                 
    -> order by c.companyname;                    
+---------+------------------------------+-----------+
| orderid | companyname                  | lastname  |
+---------+------------------------------+-----------+
|   10578 | B's Beverages                | Peacock   |
|   10280 | Berglunds snabbkop           | Fuller    |
|   10924 | Berglunds snabbkop           | Leverling |
|   10970 | Bolido Comidas preparadas    | Dodsworth |
|   10663 | Bon app'                     | Fuller    |
|   10827 | Bon app'                     | Davolio   |
|   10726 | Eastern Connection           | Peacock   |
|   10264 | Folk och fa HB               | Suyama    |
|   10807 | Franchi S.p.A.               | Peacock   |
|   10423 | Gourmet Lanchonetes          | Suyama    |
|   10709 | Gourmet Lanchonetes          | Davolio   |
|   10777 | Gourmet Lanchonetes          | King      |
|   10816 | Great Lakes Food Market      | Peacock   |
|   10960 | HILARION-Abastos             | Leverling |
|   10705 | HILARION-Abastos             | Dodsworth |
|   10660 | Hungry Coyote Import Store   | Callahan  |
|   10309 | Hungry Owl All-Night Grocers | Leverling |
|   10687 | Hungry Owl All-Night Grocers | Dodsworth |
|   10380 | Hungry Owl All-Night Grocers | Callahan  |
|   10749 | Island Trading               | Peacock   |
|   10927 | La corne d'abondance         | Peacock   |
|   10545 | Lazy K Kountry Store         | Callahan  |
|   10593 | Lehmanns Marktstand          | King      |
|   10779 | Morgenstern Gesundkost       | Leverling |
|   10427 | Piccolo und mehr             | Peacock   |
|   10433 | Princesa Isabel Vinhos       | Leverling |
|   10515 | QUICK-Stop                   | Fuller    |
|   10451 | QUICK-Stop                   | Peacock   |
|   10828 | Rancho grande                | Dodsworth |
|   10727 | Reggiani Caseifici           | Fuller    |
|   10847 | Save-a-lot Markets           | Peacock   |
|   10523 | Seven Seas Imports           | King      |
|   10271 | Split Rail Beer & Ale        | Suyama    |
|   10302 | Supremes delices             | Peacock   |
|   10320 | Wartian Herkku               | Buchanan  |
|   10483 | White Clover Markets         | King      |
|   10596 | White Clover Markets         | Callahan  |
+---------+------------------------------+-----------+
37 rows in set (0.008 sec)

1997年以後那些公司名稱，訂單數量大於十五張以上，並且依照訂單數量大到小排序

//(group by搭配having)
MariaDB [northwind]> select c.companyname, count(o.orderid) nums
    -> from customers c
    -> join orders o on (c.customerid = o.customerid)
    -> where o.orderdate >= '1997-01-01'
    -> group by c.companyname
    -> having nums >= 15
    -> order by nums desc;
+--------------------+------+
| companyname        | nums |
+--------------------+------+
| Save-a-lot Markets |   28 |
| Ernst Handel       |   24 |
| QUICK-Stop         |   22 |
| Folk och fa HB     |   16 |
| HILARION-Abastos   |   16 |
| Berglunds snabbkop |   15 |
+--------------------+------+
6 rows in set (0.006 sec)

單一品項有超過一萬塊的公司訂單

#那些公司下的訂單實際銷售金額超過一萬塊有哪些公司
MariaDB [northwind]> select c.companyname,o.orderid,
    -> od.unitprice*od.quantity*(1-od.discount) realprice
    -> from `order details`
    -> od
    -> join orders o on (o.orderid = od.orderid)
    -> join customers c on ( c.customerid = o.customerid)
    -> where od.unitprice*od.quantity*(1-od.discount) > 10000
    -> order by realprice desc;
+----------------------------+---------+--------------------+
| companyname                | orderid | realprice          |
+----------------------------+---------+--------------------+
| Hanari Carnes              |   10981 |              15810 |
| QUICK-Stop                 |   10865 | 15019.499988220632 |
| Simons bistro              |   10417 |              10540 |
| Rattlesnake Canyon Grocery |   10889 |              10540 |
+----------------------------+---------+--------------------+
4 rows in set (0.073 sec)